/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author Y.Yang
 */

import java.sql.*;

public class ConnectionBean {
    /**
	 * Attempts to establish a connection to your database.
	 * @return a Connection to your database.
	 * @throws SQLException if a database access error occurs.
	 */
	public static Connection db = null;
	
	protected static String url = "jdbc:postgresql://localhost:5432/postgres";
	
	protected static String username = "postgres";
	
	protected static String password = "csi508";
	
	
	
	protected static Connection getConnectionToYourDB() throws SQLException {
		// (4 points) complete the following line to establish a connection to your database.
		/*
		try{
			Class.forName("org.postgresql.Driver");
		}catch(ClassNotFoundException cnfe){
			System.out.println("Could not find the JDBC driver");
			System.exit(1);
		}
		*/
           db = DriverManager.getConnection(url, username, password);
		
           
            return null;
		
	}
	
	/**
	 * Executes the query in Part 3 (a).
	 * @param connection a Connection to your database.
	 * @return the ResultSet an object that contains the data produced by the query.
	 * @throws SQLException if a database access error occurs or 
	 *   the given SQL statement produces anything other than a single ResultSet object
	 */
	protected static ResultSet getResultOfPart3A(Connection connection) throws SQLException {
		// (4 points) complete the following line to return a ResultSet obtained by running 
		// the query in Part 3 (a).
		Statement st = db.createStatement();
		ResultSet rs = st.executeQuery("select distinct customer_name from customer natural join depositor natural join account where balance >= 6000;");
		/*
		while (rs.next()){
			//System.out.print("Part 3 (a) returned");
			System.out.println(rs.getString(1));
		}
		
		rs.close();
		st.close();
		*/
		return rs;
	}
	
	/**
	 * Executes the query in Part 3 (b).
	 * @param connection a Connection to your database.
	 * @return the ResultSet an object that contains the data produced by the query.
	 * @throws SQLException if a database access error occurs or 
	 *   the given SQL statement produces anything other than a single ResultSet object
	 */
	protected static ResultSet getResultOfPart3B(Connection connection) throws SQLException {
		// (4 points) complete the following line to return a ResultSet obtained by running 
		// the query in Part 3 (b).
		Statement st = db.createStatement();
		ResultSet rs = st.executeQuery("select    distinct account_number from      account natural join depositor  group by  account_number having    count(depositor.customer_id) >= 2;");
		return rs;
	}

	public static void main(String[] argv) {
		try {
			Connection connection = getConnectionToYourDB();

			// Part 3 (a)
			System.out.println("The names of all all depositors who have an account with a balance greater than $6,000:");
			ResultSet rs = getResultOfPart3A(connection);
			// (4 points) complete a loop that prints (using System.out.println()) all the relevant customer names.
			while (rs.next()){
				System.out.println(rs.getString(1));
			}
			
			System.out.println();
		
			// Part 3 (b)
			System.out.println("The the account numbers of all joint accounts (i.e., accounts owned by at least two customers):");
			rs = getResultOfPart3B(connection);
			// (4 points) complete a loop that prints (using System.out.println()) all the relevant account numbers.
			while (rs.next()){
				System.out.println(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
        
    
}
